<?php

// Copyright (c) 2012 Mirza Adipradhana


//Menyatakan apakah DEBUGGER telah terLOAD
define("MYSQL_DBQUERY_PARSE_LOADED", true);

class DBQueryParse extends MySqlDatabase {

    private $mTableName;
    private $mCondition= '1=1';
    private $mGroupBy;
    private $mOrderBy;
    
    private $mField = array();
    private $mOrder = array();
    private $mFieldId;
    private $mFieldPointer;
    private $mOrderPointer;
    private $mIsAutoClose;
    private $mIsViewAll= false;

    public function __construct($pIsAutoClose = false) {
        $this->host = DBHostName;
        $this->database = DBName;
        $this->user = DBUserName;
        $this->password = DBPassword;

        $this->mIsAutoClose = $pIsAutoClose;
        $this->mFieldId= 0;
        $this->ClearSQL();
        $this->ConnectDB();
    }

    public function GenerateSQL() {
        $tFungsi= new Fungsi();
        $tFungsi->CalibrateArray($this->mField);
        
        $tIsConditionExist = false;
        $tCondition = array();
        $tSQL = "SELECT ";
        $tField = $this->GetFieldDML();

        //CHECK: Field table harus ter set
        if (!empty($tField)) {
            $tSQL.=$tField . " ";
            //CHECK: Nama table harus ter set
            if (!empty($this->mTableName)) {
                $tSQL.= " FROM " . $this->mTableName . " ";

                /*
                 * CHECK: apakah terdapat kondisi 
                 * CHECK FIRST: APAKAH HASIL SEARCH?
                 */

                //CHECK: APAKAH HASIL SEARCH? 
                if ($_POST[TEMPLATE_SETTING_SEARCH_CATEGORY_VAR]>=0 && $_POST[TEMPLATE_SETTING_SEARCH_KEYWORDS_VAR]) {
                    //VALIDASI HASIL SEARCH
                    $tValidation = new InputValidation();

                    $tCat = $_POST[TEMPLATE_SETTING_SEARCH_CATEGORY_VAR];
                    $tValidation->SetInput($tCat);
                    $tValidation->SetPattern(pUnsignedInt);
                    $tCat = $tValidation->CheckValidation();

                    $tKey = $_POST[TEMPLATE_SETTING_SEARCH_KEYWORDS_VAR];
                    $tValidation->SetInput($tKey);
                    $tValidation->SetPattern(pStringKeywords);
                    $tKey = $tValidation->CheckValidation();
                    
                    unset($temp);
                    if ($tCat || $tKey) {
                        $tIsConditionExist = true;
                        if($tCat==0){
                            //ANY KEYWORDS CATEGORY
                            foreach($this->mField as $tRes){
                                if(isset($temp))
                                    $temp.= " OR ";
                                $temp.= " " . $tRes[FieldDB] . " LIKE \"%" . $tKey . "%\" ";
                            }
                        }
                        else
                            $temp = " " . $this->mField[$tCat][FieldDB] . " LIKE \"%" . $tKey . "%\" "; //echo $temp.'<br>';
                        array_push($tCondition, $temp);
                    }
                }
                
                /*//CHECK: APAKAH TERDAPAT KONDISI?
                if (!empty($this->mCondition)) {
                    $tIsConditionExist = true;
                    array_push($tCondition, $this->mCondition);
                }

                //CREATE CONDITION QUERY
                if ($tIsConditionExist) {

                    //PARSE CONDITION
                    $tResult = implode(" AND ", $tCondition);
                    if ($tResult) {
                        $tSQL.= "WHERE " . $tResult;
                    } else {
                        //ERROR: HASIL PARSING TIDAK BERHASIL
                    }
                }*/

                //CHECK: apakah terdapat order
                if (!empty($this->mOrder)) {
                    $tSQL_Order.= "ORDER BY " . $this->mOrder . " ";
                }
            } else {
                //ERROR: Nama Table belum ada
            }
        } else {
            //ERROR: Field Table belum ada
        }
        
        //ADDING CONDITION if exist
        //BUG: jika terdapat substr
        /*echo 'pos= '.strpos($tSQL, "WHERE");
        if(strpos($tSQL, "WHERE") ){
            $tSQL.= $this->mCondition;
        }
        else {
            $tSQL.= " WHERE ".$this->mCondition;
        }*/
        $tSQL.= " WHERE ".$this->mCondition;

        //ADDING GROUP BY if exist
        if($this->mGroupBy)
            $tSQL.= " GROUP BY ".$this->mGroupBy;
        
        //ADDING ORDER BY if exist
        if($this->mOrderBy)
            $tSQL.= " ORDER BY ".$this->mOrderBy;
        
        $this->mSQL = $tSQL;
        
        //DEBUGGER:
        if(CORE_DEVELOPING_MODE)
            echo '<div class="Debug">'.$tSQL.'</div>';
        
        return $tSQL;
    }

    public function ParseSQL($pCurrentPage=0) {
        $tFungsi= new Fungsi();
        $tFungsi->CalibrateArray($this->mField);
        
        if(!$pCurrentPage)
            $pCurrentPage=0;
        $tResult = array();
        $tIsOpen = $this->IsOpen();
        //PERIKSA: koneksi dengan database. Reconnect DB		
        if (!$tIsOpen) {
            //Lakukan Reconnect jika Status Koneksi = 0
            $this->ConnectDB();
        }

        if(!$this->mSQL)
            $tSQL = $this->GenerateSQL();
        else 
            $tSQL = $this->mSQL;
        
        //CHECK: Apakah paging berlaku
        if (TEMPLATE_SETTING_LIST_PAGING_MAXIMUMPAGE > 0 && !$this->mIsViewAll) {
            $tSQL_Overall= $tSQL;
            
            //ADDING: PAGING / SHOW LIMIT
            $tSQL.= " LIMIT ".($pCurrentPage*TEMPLATE_SETTING_LIST_PAGING_MAXIMUMPAGE).",".TEMPLATE_SETTING_LIST_PAGING_MAXIMUMPAGE;
            
            $tResult = $this->Execute($tSQL, $tSQL_Overall);
            
        } else {
            //Lakukan Eksekusi SQL 
            $tResult = $this->Execute($tSQL);
        }
        
        
        //DO: Close DB Connection jika $mIsAutoClose = true
        if ($this->mIsAutoClose)
            $this->Close();

        

        return $tResult;
    }

    public function GetPointer() {
        echo 'POINTER: ' . $this->mFieldPointer . '<br>';
    }

    public function SetTableCondition($pCondition) {
        $this->mCondition = $pCondition;
    }
    
    public function SetTableGroup($pBy) {
        $this->mGroupBy = $pBy;
    }
    
    public function SetTableOrder($pBy) {
        $this->mOrderBy = $pBy;
    }
    
    public function SetTableName($pName) {
        $this->mTableName = $pName;
    }

    public function SetViewAll($pValue) {
        $this->mIsViewAll = $pValue;
    }

    private function GetFieldIndexOf() {
        $tCount = count($this->mField);
        (!empty($tCount)) ? $tIndex = $tCount : $tIndex = 0;

        return $tIndex;
    }


    public function CreateField() {
        $tFungsi= new Fungsi();
        
        $tFungsi->CalibrateArray($this->mField);
        $tIndex = $tFungsi->GetArrayIndexOf($this->mField);
        
        //CHECK: JIKA nilai pointer belum memiliki nilai
        if (!empty($this->mFieldPointer)) {
            //Karena nilai pointer sudah ada, maka buat yang baru
            $tIndex++;
        }
        $this->mFieldPointer = $tIndex;
    }
    
    
    public function CountField(){
        $tFungsi= new Fungsi();
        $tFungsi->CalibrateArray($this->mField);
        
        return count($this->mField);
    }

    public function SetFieldIndexAsFieldId($pIndexField){
        $this->mFieldId= $pIndexField;     
    }
    
    public function GetFieldIndexAsFieldId(){
        return $this->mFieldId;
    }
    
    public function SetFieldDB($pField, $pIndex = null) {
        if (!is_int($pIndex)) {
            //CHECK: jika Index tidak diisikan (otomatis nge-generate), 
            // JIKA pointer belum memiliki nilai, maka generate
            if (empty($this->mFieldPointer))
                $pIndex = $this->CreateField();

            $pIndex = $this->mFieldPointer;
        }
        $this->mField[$pIndex]['FieldDB'] = $pField;
    }

    public function SetFieldHeader($pField, $pIndex = null) {
        if (!is_int($pIndex))
            $pIndex = $this->mFieldPointer;
        $this->mField[$pIndex]['FieldName'] = $pField;
    }

    public function GetFieldHeader($pIndex) {
        return $this->mField[$pIndex]['FieldName'];
    }


    public function GetFieldDML() {
        $tFungsi= new Fungsi();
        $tFungsi->CalibrateArray($this->mField);
        
        $tResult = '';
        $i = 0;
        if (!empty($this->mField)) {
            foreach ($this->mField as $tValue) {
                $tResult.= $tValue[FieldDB];
                if (!empty($this->mField[$i + 1][FieldDB])) {
                    $tResult.= ", ";
                }
                $i++;
            }
        }
        return $tResult;
    }

    public function MoveIndexFieldFirst($pIndex) {
        $tFungsi= new Fungsi();
        $tFungsi->CalibrateArray($this->mField);

        if (is_int($pIndex)) {
            $tTemp = $this->mField;
            if ($tTemp) {
                $tIndexDest = 0;

                if ($pIndex >= 0) {
                    //SIMPAN DATA YANG AKAN DIPINDAHKAN
                    $tMoveData = $this->mField[$pIndex];

                    //RESORTING DATA, data bergeser mundur
                    //Eksekusi hanya dilakukan pada data SEBELUM data yang akan dipindahkan.
                    //Dimulai dari belakang
                    $i = $pIndex - 1;
                    while ($this->mField[$i] && $i >= 0) {
                        $this->mField[$i + 1] = $this->mField[$i];
                        $i--;
                    }

                    //EKSEKUSI PEMINDAHAN DATA YANG PERTAMA<=>
                    $this->mField[$tIndexDest] = $tMoveData;
                } else {
                    // ERROR: INDEX TARGET DILUAR BATAS
                }
            } else {
                // ERROR: INDEX TIDAK DITEMUKAN
            }
        } else {
            // ERROR: INDEX BUKAN INTEGER
        }
    }

    public function MoveIndexFieldLast($pIndex) {
        $tFungsi= new Fungsi();
        $tFungsi->CalibrateArray($this->mField);

        if (is_int($pIndex)) {
            $tTemp = $this->mField;
            if ($tTemp) {
                $tIndexDest = count($this->mField) - 1;

                if ($pIndex >= 0) {
                    //SIMPAN DATA YANG AKAN DIPINDAHKAN
                    $tMoveData = $this->mField[$pIndex];

                    //RESORTING DATA, data bergeser maju
                    //Eksekusi hanya dilakukan pada data SETELAH data yang akan dipindahkan.
                    $i = $pIndex + 1;
                    while ($this->mField[$i]) {
                        $this->mField[$i - 1] = $this->mField[$i];
                        $i++;
                    }

                    //EKSEKUSI PEMINDAHAN DATA YANG TERAKHIR<=>
                    $this->mField[$tIndexDest] = $tMoveData;
                } else {
                    // ERROR: INDEX TARGET DILUAR BATAS
                }
            } else {
                // ERROR: INDEX TIDAK DITEMUKAN
            }
        } else {
            // ERROR: INDEX BUKAN INTEGER
        }
    }

    public function MoveIndexFieldUp($pIndex) {
        $tFungsi= new Fungsi();
        $tFungsi->CalibrateArray($this->mField);

        if (is_int($pIndex)) {
            $tTemp = $this->mField;
            if ($tTemp) {
                $tIndexDest = $pIndex - 1;

                if ($pIndex >= 0) {
                    $tMoveData = $this->mField[$pIndex];
                    $tRelocateData = $this->mField[$tIndexDest];

                    //EKSEKUSI PEMINDAHAN DATA <=>
                    $this->mField[$tIndexDest] = $tMoveData;
                    $this->mField[$pIndex] = $tRelocateData;
                } else {
                    // ERROR: INDEX TARGET DILUAR BATAS
                }
            } else {
                // ERROR: INDEX TIDAK DITEMUKAN
            }
        } else {
            // ERROR: INDEX BUKAN INTEGER
        }
    }

    public function MoveIndexFieldDown($pIndex) {
        $tFungsi= new Fungsi();
        $tFungsi->CalibrateArray($this->mField);

        if (is_int($pIndex)) {
            $tTemp = $this->mField;
            if ($tTemp) {
                $tIndexDest = $pIndex + 1;

                $tMoveData = $this->mField[$pIndex];
                $tRelocateData = $this->mField[$tIndexDest];

                //EKSEKUSI PEMINDAHAN DATA <=>
                $this->mField[$tIndexDest] = $tMoveData;
                $this->mField[$pIndex] = $tRelocateData;
            } else {
                // ERROR: INDEX TIDAK DITEMUKAN
            }
        } else {
            // ERROR: INDEX BUKAN INTEGER
        }
    }

    public function PrintField() {
        $tField = $this->mField;

        if (count($tField > 0)) {
            echo '<table style="border:none; border-bottom:1px solid; border-top:1px solid;">';
            foreach ($tField as $tIndex => $temp) {
                echo '<tr>';
                echo '<td valign="top"><h3>' . $tIndex . '</h3></td>';
                echo '<td>';

                foreach ($tField[$tIndex] as $tKey => $tValue) {
                    echo '<b>' . $tKey . ' : </b> ' . $tValue . '<br>';
                }
                echo '</td>';
                echo '</tr>';
            }
            echo '<table>';
        }
    }

    private function GetOrderIndexOf() {
        $tCount = count($this->mOrder);
        (!empty($tCount)) ? $tIndex = $tCount : $tIndex = 0;

        return $tIndex;
    }

    public function CreateOrder() {
        $tIndex = $this->GetOrderIndexOf();

        //CHECK: JIKA nilai pointer belum memiliki nilai
        if (!empty($this->mOrderPointer)) {
            //Karena nilai pointer sudah ada, maka buat yang baru
            $tIndex++;
        }

        $this->mOrderPointer = $tIndex;
    }

    public function CalibrateOrder() {
        $i = 0;
        $tTemp = array();
        foreach ($this->mOrder as $tRes) {
            $tTemp[$i] = $tRes;
            $i++;
        }

        //PERIKSA apakah ada hasil dari calibrating, jika ya eksekusi
        if ($tTemp) {
            //RESET DATA FIELD
            $this->mOrder = $tTemp;
        }
    }

    
    public function GetField() {
        $tFungsi= new Fungsi();
        $tFungsi->CalibrateArray($this->mField);
        
        $tField = $this->mField;
        $tRes = array();

        if (count($tField > 0)) {
            foreach ($tField as $tIndex => $temp) {
                foreach ($tField[$tIndex] as $tKey => $tValue) {
                    $tRes[$tIndex][$tKey] = $tValue;
                }
            }
        }

        return $tRes;
    }

}